﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LoadNorthwindDB;
using Microsoft.SqlServer.Management.Common;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;

namespace Ex10.ExecuteStoredProcedure
{
    public class ExecuteUsp
    {
        static void Main(string[] args)
        {
            RunStoredProc();
            Console.Read();
        }

        public static void RunStoredProc()
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;

            try
            {

                conn = new SqlConnection("Server=CHRIS-PC\\SQLEXPRESS;DataBase=Northwind;Integrated Security=true");
                conn.Open();
                DateTime startDate = new DateTime(1996, 1, 1);
                DateTime endDate = new DateTime(1998, 1, 1);
                string supplier = "Yoshi Nagase";
                int result = 0;
                string command = string.Format("dbo.usp_FindTotalIncomeForSuplierAndPeriod {0} {1} {2} {3} ",
                    supplier,startDate.ToShortDateString(),endDate.ToShortDateString(),result);
                SqlCommand cmd = new SqlCommand(command, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                rdr = cmd.ExecuteReader();
                Console.WriteLine(result);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
    }
}
